﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data.OleDb;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;

namespace KPIS.GERP.GHRMS.DAL
{
    public sealed class AbsenceRuleDAL : IAbsenceRuleDAL
    {

        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        
        public AbsenceRuleDAL()
        {            
            
        }

        public IList<rule_absence> GetAbsenceRulesByEmpSeq(string emp_seq)
        {
            string strSQL;
            List<rule_absence> result = new List<rule_absence>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                // dbManager.AddParameters(0, "@Sequence", absence_type_seq);
                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@EmpSeq", emp_seq);
                //SELECT r.* FROM rule_absence r LEFT JOIN hrm_ps_employees e ON r.emp_type_seq = e.emp_type_seq WHERE e.emp_seq = 1
                strSQL = "SELECT r.*"
                    + " FROM hrm_ps_absence_rules r LEFT JOIN HRM_PS_EMPLOYEES e ON r.EMP_TYPE_SEQ = e.EMP_TYPE_SEQ"
                    + " WHERE r.RECORD_STATUS not like @DeleteRecord"
                    + (emp_seq != null ? " AND e.EMP_SEQ = @EmpSeq" : "");
                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                // result = (List<rule_absence>)CollectionHelper.ConvertTo<rule_absence>(dtb);
                result = new List<rule_absence>();
                foreach (DataRow dr in dtb.Rows)
                {
                    rule_absence data = new rule_absence();
                    data.rule_absence_seq = Convert.ToInt32(dr["rule_seq"]);
                    data.emp_type_seq = Convert.ToInt32(dr["emp_type_seq"]);
                    data.absence_type_seq = Convert.ToInt32(dr["absence_type_seq"]);
                    data.send_before = ConvertNullInt(dr["send_before"]);
                    data.day_limit = ConvertNullDecimal(dr["quota_day"]);
                    data.day_limit_full_salary = ConvertNullDecimal(dr["quota_salary"]);
                    data.day_limit_half_salary = ConvertNullDecimal(dr["quota_half_salary"]);
                    data.time_limit = ConvertNullDecimal(dr["quota_count"]);
                    data.day_limit = ConvertNullDecimal(dr["quota_day_count"]);
                    data.remarks = Convert.ToString(dr["remarks"]);
                    data.record_status = Convert.ToString(dr["record_status"]);
                    data.created_by = ConvertNullInt(dr["created_by"]);
                    data.created_when = ConvertNullDateTime(dr["created_when"]);
                    data.updated_by = ConvertNullInt(dr["updated_by"]);
                    data.updated_when = ConvertNullDateTime(dr["updated_when"]);
                    data.include_nwd = Convert.ToBoolean(dr["include_nwd"]) ? "1" : "0";
                    data.quota_day_carry_type = Convert.ToInt32(dr["quota_day_carry_type"]);
                    data.quota_day_carry = ConvertNullInt(dr["quota_day_carry"]);
                    data.gender_seq = ConvertNullInt(dr["gender_seq"]);
                }

                return result;
        
            }
            catch (Exception ex)
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public int? ConvertNullInt(object o)
        {
            int tempInt;
            if (Int32.TryParse(Convert.ToString(o), out tempInt))
                return tempInt;
            else
            {
                return null;
            }
        }
        public decimal? ConvertNullDecimal(object o)
        {
            decimal tempDecimal;
            if (Decimal.TryParse(Convert.ToString(o), out tempDecimal))
                return tempDecimal;
            else
            {
                return null;
            }
        }
        public DateTime? ConvertNullDateTime(object o)
        {
            DateTime tempDateTime;
            if (DateTime.TryParse(Convert.ToString(o), out tempDateTime))
                return tempDateTime;
            else
            {
                return null;
            }
        }

        public IList<rule_absence> GetAbsenceRules(string emp_type_seq)
        {
            string strSQL;
            List<rule_absence> result = new List<rule_absence>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

               // dbManager.AddParameters(0, "@Sequence", absence_type_seq);
                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@EmpType", emp_type_seq);

                strSQL = "SELECT *"
                    + " FROM RULE_ABSENCE"
                    + " WHERE RECORD_STATUS not like @DeleteRecord"                  
                    + (emp_type_seq != null ? " AND EMP_TYPE_SEQ = @EmpType" : "");
                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                result = (List<rule_absence>)CollectionHelper.ConvertTo<rule_absence>(dtb);

                return result;
                //IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                //while (rdr.Read())
                //{
                //    EmployeeTypeDAL etDAL = new EmployeeTypeDAL();
                //    EmployeeTypeInfo etInfo = new EmployeeTypeInfo();
                //    if (!rdr.IsDBNull(1))
                //    {
                //        IList<EmployeeTypeInfo> etList = etDAL.ListEmployeeTypes(new EmployeeTypeInfo(rdr.GetInt32(1)));
                //        etInfo = etList[0];
                //    }
                //    AbsenceTypeDAL abDAL = new AbsenceTypeDAL();
                //    AbsenceTypeInfo abInfo = new AbsenceTypeInfo();
                //    if (!rdr.IsDBNull(2))
                //    {
                //        IList<AbsenceTypeInfo> abList = abDAL.ListAbsenceTypes(new AbsenceTypeInfo(rdr.GetInt32(2)));
                //        abInfo = abList[0];
                //    }

                //    AbsenceRuleInfo Info = new AbsenceRuleInfo(rdr.GetInt32(0)
                //        , etInfo, abInfo
                //        , rdr.IsDBNull(3) ? 0 : rdr.GetDecimal(3)
                //        , rdr.IsDBNull(4) ? 0 : rdr.GetDecimal(4)
                //        , rdr.IsDBNull(5) ? 0 : rdr.GetDecimal(5)
                //        , rdr.IsDBNull(6) ? 0 : rdr.GetDecimal(6)
                //        , rdr.IsDBNull(7) ? "" : rdr.GetString(7)
                //        , rdr.IsDBNull(8) ? "" : rdr.GetString(8)
                //        , rdr.IsDBNull(9) ? (int?)null : rdr.GetInt32(9)
                //        , rdr.IsDBNull(10) ? (int?)null : rdr.GetInt32(10)
                //        );
                //    infoList.Add(Info);
                //}

                //return infoList;
            }
            catch (Exception ex)
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public IList<rule_absence> GetAbsenceRules(string emp_type_seq, string absence_type_seq)
        {
            string strSQL;
            List<rule_absence> result = new List<rule_absence>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@Sequence", absence_type_seq);
                dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@EmpType", emp_type_seq);

                strSQL = "SELECT *"
                    + " FROM RULE_ABSENCE"                        
                    + " WHERE RECORD_STATUS not like @DeleteRecord"
                    + (absence_type_seq != null ? " AND RULE_ABSENCE_SEQ = @Sequence" : "")
                    + (emp_type_seq  != null ? " AND EMP_TYPE_SEQ = @EmpType" : "");
                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                result = (List<rule_absence>)CollectionHelper.ConvertTo<rule_absence>(dtb);

                return result;
                //IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                //while (rdr.Read())
                //{
                //    EmployeeTypeDAL etDAL = new EmployeeTypeDAL();
                //    EmployeeTypeInfo etInfo = new EmployeeTypeInfo();
                //    if (!rdr.IsDBNull(1))
                //    {
                //        IList<EmployeeTypeInfo> etList = etDAL.ListEmployeeTypes(new EmployeeTypeInfo(rdr.GetInt32(1)));
                //        etInfo = etList[0];
                //    }
                //    AbsenceTypeDAL abDAL = new AbsenceTypeDAL();
                //    AbsenceTypeInfo abInfo = new AbsenceTypeInfo();
                //    if (!rdr.IsDBNull(2))
                //    {
                //        IList<AbsenceTypeInfo> abList = abDAL.ListAbsenceTypes(new AbsenceTypeInfo(rdr.GetInt32(2)));
                //        abInfo = abList[0];
                //    }

                //    AbsenceRuleInfo Info = new AbsenceRuleInfo(rdr.GetInt32(0)
                //        , etInfo, abInfo
                //        , rdr.IsDBNull(3) ? 0 : rdr.GetDecimal(3)
                //        , rdr.IsDBNull(4) ? 0 : rdr.GetDecimal(4)
                //        , rdr.IsDBNull(5) ? 0 : rdr.GetDecimal(5)
                //        , rdr.IsDBNull(6) ? 0 : rdr.GetDecimal(6)
                //        , rdr.IsDBNull(7) ? "" : rdr.GetString(7)
                //        , rdr.IsDBNull(8) ? "" : rdr.GetString(8)
                //        , rdr.IsDBNull(9) ? (int?)null : rdr.GetInt32(9)
                //        , rdr.IsDBNull(10) ? (int?)null : rdr.GetInt32(10)
                //        );
                //    infoList.Add(Info);
                //}

                //return infoList;
            }
            catch (Exception ex)
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public IList<AbsenceRuleInfo> ListAbsenceRules(AbsenceRuleInfo ruleInfo)
        {
            string strSQL;
            int EmpType = 0;

            if (ruleInfo.EmployeeType != null)
                EmpType = ruleInfo.EmployeeType.Sequence;

            List<AbsenceRuleInfo> infoList = new List<AbsenceRuleInfo>();

                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@Sequence", ruleInfo.Sequence);
                dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@EmpType", EmpType);

            strSQL = @" SELECT r.RULE_SEQ, r.EMP_TYPE_SEQ, r.ABSENCE_TYPE_SEQ, r.QUOTA_DAY, r.QUOTA_SALARY, r.QUOTA_COUNT, r.QUOTA_DAY_COUNT 
                            , r.REMARKS, r.RECORD_STATUS, r.CREATED_BY, r.UPDATED_BY, r.quota_half_salary, r.include_nwd 
                            , r.send_before, r.GENDER_SEQ
                        FROM HRM_PS_ABSENCE_RULES r 
                            INNER JOIN HRM_PS_EMPLOYEE_TYPES et ON r.EMP_TYPE_SEQ = et.EMP_TYPE_SEQ 
                            INNER JOIN HRM_PS_ABSENCE_TYPES at ON r.ABSENCE_TYPE_SEQ = at.ABSENCE_TYPE_SEQ 
                        WHERE r.RECORD_STATUS not like @DeleteRecord 
                            AND et.RECORD_STATUS not like @DeleteRecord 
                            AND at.RECORD_STATUS not like @DeleteRecord "
                     + (ruleInfo.Sequence > 0 ? " AND r.RULE_SEQ = @Sequence" : "")
                     + (EmpType > 0 ? " AND r.EMP_TYPE_SEQ = @EmpType" : "")
                     + "  ORDER BY at.ABSENCE_TYPE_SEQ, r.EMP_TYPE_SEQ";

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    EmployeeTypeDAL etDAL = new EmployeeTypeDAL();
                    EmployeeTypeInfo etInfo = new EmployeeTypeInfo();
                    if (!rdr.IsDBNull(1))
                    {
                        int seq = rdr.GetInt32(1);
                        IList<EmployeeTypeInfo> etList = etDAL.ListEmployeeTypes(new EmployeeTypeInfo(seq));
                        etInfo = etList[0];
                    }
                    AbsenceTypeDAL abDAL = new AbsenceTypeDAL();
                    AbsenceTypeInfo abInfo = new AbsenceTypeInfo();
                    if (!rdr.IsDBNull(2))
                    {
                        IList<AbsenceTypeInfo> abList = abDAL.ListAbsenceTypes(new AbsenceTypeInfo(rdr.GetInt32(2)));
                        abInfo = abList.FirstOrDefault();
                    }

                    AbsenceRuleInfo Info = new AbsenceRuleInfo(rdr.GetInt32(0)
                                                               , etInfo, abInfo
                                                               , rdr.IsDBNull(3) ? 0 : rdr.GetDecimal(3)
                                                               , rdr.IsDBNull(4) ? 0 : rdr.GetDecimal(4)
                                                               , rdr.IsDBNull(11) ? 0 : rdr.GetDecimal(11)
                                                               , rdr.IsDBNull(5) ? 0 : rdr.GetDecimal(5)
                                                               , rdr.IsDBNull(6) ? 0 : rdr.GetDecimal(6)
                                                               , rdr.IsDBNull(7) ? "" : rdr.GetString(7)
                                                               , rdr.IsDBNull(8) ? "" : rdr.GetString(8)
                                                               , rdr.IsDBNull(9) ? (int?) null : rdr.GetInt32(9)
                                                               , rdr.IsDBNull(10) ? (int?) null : rdr.GetInt32(10)
                                                               , rdr.GetBoolean(12)
                        )
                        {
                            SendBefore = rdr.IsDBNull(13) ? (int?) null : rdr.GetInt32(13),
                            GenderSeq = rdr.IsDBNull(14) ? (int?) null : rdr.GetInt32(14)
                        };
                    infoList.Add(Info);
                }
            
                dbManager.CloseReader();
                dbManager.Dispose();
                return infoList;
            //}
            //catch(Exception ex)
            //{
            //    throw ex;
            //}
            //finally
            //{
            //    dbManager.CloseReader();
            //    dbManager.Dispose();
            //}
        }

        public void Insert(AbsenceRuleInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(13);

                dbManager.AddParameters(0, "@EmpTypeSeq", Info.EmployeeType.Sequence);
                dbManager.AddParameters(1, "@AbsenceTypeSeq", Info.AbsenceType.Sequence);
                dbManager.AddParameters(2, "@QuotaDay", Info.QuotaDay);
                dbManager.AddParameters(3, "@QuotaSalary", Info.QuotaDaySalary);
                dbManager.AddParameters(4, "@QuotaCount", Info.QuotaCount);
                dbManager.AddParameters(5, "@QuotaDayCount", Info.DayPerCount);
                dbManager.AddParameters(6, "@Remarks", Info.Remarks);
                dbManager.AddParameters(7, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(8, "@CreatedBy", Info.CreatedBy);
                dbManager.AddParameters(9, "@CreatedWhen", DateTime.Today);
                dbManager.AddParameters(10, "@QuotaDayHalfSalary", Info.QuotaDayHalfSalary);
                dbManager.AddParameters(11, "@IncludeNonWorkingDay", Info.IncludeNonWorkingDay);
                dbManager.AddParameters(12, "@GenderSeq", Info.GenderSeq);

                strSQL = "INSERT INTO HRM_PS_ABSENCE_RULES(EMP_TYPE_SEQ, ABSENCE_TYPE_SEQ"
                        + ", QUOTA_DAY, QUOTA_SALARY, QUOTA_COUNT, QUOTA_DAY_COUNT"
                        + ", REMARKS, RECORD_STATUS, CREATED_BY, CREATED_WHEN, quota_half_salary, include_nwd, gender_seq)"
                    + " VALUES(@EmpTypeSeq, @AbsenceTypeSeq, @QuotaDay, @QuotaSalary, @QuotaCount"
                        + ", @QuotaDayCount, @Remarks, @RecordStatus, @CreatedBy, @CreatedWhen, @QuotaDayHalfSalary, @IncludeNonWorkingDay" +
                         ", @GenderSeq )";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public void Update(AbsenceRuleInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(14);

                dbManager.AddParameters(0, "@Sequence", Info.Sequence);
                dbManager.AddParameters(1, "@EmpTypeSeq", Info.EmployeeType.Sequence);
                dbManager.AddParameters(2, "@AbsenceTypeSeq", Info.AbsenceType.Sequence);
                dbManager.AddParameters(3, "@QuotaDay", Info.QuotaDay);
                dbManager.AddParameters(4, "@QuotaSalary", Info.QuotaDaySalary);
                dbManager.AddParameters(5, "@QuotaCount", Info.QuotaCount);
                dbManager.AddParameters(6, "@QuotaDayCount", Info.DayPerCount);
                dbManager.AddParameters(7, "@Remarks", Info.Remarks);
                dbManager.AddParameters(8, "@UpdatedBy", Info.UpdatedBy);
                dbManager.AddParameters(9, "@UpdatedWhen", DateTime.Today);
                dbManager.AddParameters(10, "@QUOTA_HALF_SALARY", Info.QuotaDayHalfSalary);
                dbManager.AddParameters(11, "@INCLUDE_NWD", Info.IncludeNonWorkingDay);
                dbManager.AddParameters(12, "@SEND_BEFORE", Info.SendBefore);
                dbManager.AddParameters(13, "@GENDERSEQ", Info.GenderSeq);

                strSQL = "UPDATE HRM_PS_ABSENCE_RULES"
                           + " SET EMP_TYPE_SEQ = @EmpTypeSeq"
                                + ", ABSENCE_TYPE_SEQ = @AbsenceTypeSeq"
                                + ", QUOTA_DAY = @QuotaDay"
                                + ", QUOTA_SALARY = @QuotaSalary"
                                + ", QUOTA_COUNT = @QuotaCount"
                                + ", QUOTA_DAY_COUNT = @QuotaDayCount"
                                + ", REMARKS = @Remarks"
                                + ", UPDATED_BY = @UpdatedBy"
                                + ", UPDATED_WHEN = @UpdatedWhen"
                                + ", INCLUDE_NWD = @INCLUDE_NWD"
                                + ", QUOTA_HALF_SALARY = @QUOTA_HALF_SALARY"
                                + ", SEND_BEFORE = @SEND_BEFORE"
                                + ", GENDER_SEQ = @GENDERSEQ"
                           + " WHERE RULE_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public void Delete(int Sequence, int UpdatedBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(3, "@UpdateWhen", DateTime.Today);

                strSQL = "UPDATE HRM_PS_ABSENCE_RULES"
                    + " SET RECORD_STATUS = @RecordStatus"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdateWhen"
                    + " WHERE RULE_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
    }
}
